package com.sunelec.controller;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.sunelec.pojo.*;
import com.sunelec.service.ReportService;
import jxl.Workbook;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.OutputStream;
import java.sql.Time;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * @Author: 吴遵明
 * @Description: 用于报表中心的Controller
 * @Date: Create in 2018/05/08
 * @Modified By:
 */
@Controller
public class ReportController {

    @Autowired
    ReportService reportService;

    //转跳ReportCenter
    @RequestMapping("InReportCenter")
    public String InReportCenter(HttpSession session, Model model) {
        Users user = null;
        if(session.getAttribute("users") != null) {
            user = (Users)session.getAttribute("users");
        }
        model.addAttribute("users",user);
        return "reportCenter";
    }


    //转跳target所指定的页面,查询该用户的所有电站
    @RequestMapping("/ReadyTarget")
    public String ReadyStationRunTable(Model model, String leveluser, int type, String target, HttpSession session) {
        List<Power> list;
        if(type == 1) {
            list = reportService.selectPowerWithUser1();
        } else if(type == 2 && leveluser != null) {
            list = reportService.selectPowerWithUser2(leveluser);
        } else if(type == 3 && leveluser != null) {
            list = reportService.selectPowerWithUser3(leveluser);
        } else {
            System.out.println("未获得权限及用户名");
            return "reportCenter";
        }

        session.setAttribute("Power_type",list);
        model.addAttribute("powers",list);
        model.addAttribute("selected",list.get(0).getPowerId());
        if(target.equals("stationRunTable")) {
            return "stationRunTable";
        } else if(target.equals("deviceRunTable")) {
            return "deviceRunTable";
        } else if(target.equals("electricityTable")) {
            return "electricityTable";
        } else if(target.equals("faultStatisticalTable")) {
            return "faultStatisticalTable";
        } else if(target.equals("historyTable")) {
            return "historyTable";
        } else {
            return "reportCenter";
        }
    }


    //用于返回该电站该天每个时间段状态，对于stationRunTable.jsp页面
    public List<stationRunTableExample2> getPowerState(String power_id, String date) {
        List<stationRunTableExample2> result = new ArrayList<stationRunTableExample2>();
        List<stationRunTableExample> list = getInverterState(power_id,date);//获得该电站所有设备的24小时的状态
        Power p = reportService.selectPowerById(power_id);//查出电站

        for(int i=0; i<24; i++) {
            stationRunTableExample2 sta = new stationRunTableExample2();
            sta.setPower_name(p.getPowerName());
            sta.setPower_local(p.getPowerLocal());
            sta.setHour(i);
            if (i < 10) {
                sta.setTime(date + "  0" + i + ":00:00");
            } else {
                sta.setTime(date + "  " + i + ":00:00");
            }
            sta.setState("Error");
            for(stationRunTableExample st : list) {
                if(st.getHour() == i && st.getState().equals("正常")) {//只要有1个sn（设备）正常，则power（电站）正常
                    sta.setState("正常");
                    break;
                }
            }
            result.add(sta);
        }

        return result;
    }


    //用于判断该电站inverter状态
    //形参 电站id，日期(天)
    //返回 该电站所有Inverter状态
    //          可直接用于deviceRunTable.jsp页面
    public List<stationRunTableExample> getInverterState(String power_id, String date) {
        List<stationRunTableExample> result = new ArrayList<stationRunTableExample>();

        //1.根据power_name,type,levelname来查询power_id;
        Power p = reportService.selectPowerById(power_id);
        //2.根据date，power_id站点查询该user下的所有inverter类（是一天的）
        List<Inverter> list = reportService.selectInverterByDate(date,p.getPowerId());
        //3.对每一个sn进行24小时循环，默认值:异常
        int index = 0;
        //4.对每个该电站的sn进行判断，有则不管，没有则创建
        List<String> li = new ArrayList<>();
        li.add(p.getPowerId());
        List<Collector> list_temp = reportService.selectCollectorWithPowerid(li );//获得该电站的每个sn
        for(Collector i_t : list_temp) {
            boolean te = false;//默认没有该sn
            for(Inverter i : list) {
                if(i_t.getCollectorSn().equals(i.getCollectorSn())) {
                    te = true;
                    break;
                }
            }
            if(!te) {
                Inverter in = new Inverter();
                in.setCollectorSn(i_t.getCollectorSn());
                list.add(in);
            }
        }
        //5.结果
        for(Inverter inver : list) {
            boolean temp = true;
            for(stationRunTableExample s : result) {
                if(inver.getCollectorSn().equals(s.getPower_sn())) {//如果有一个sn是匹配的，就不能24循环
                    temp = false;
                }
            }
            if(index == 0)  temp = true;
            if(temp) {
                for (int i = 0; i < 24; i++) {
                    stationRunTableExample st = new stationRunTableExample();
                    st.setPower_sn(inver.getCollectorSn());
                    st.setPower_name(p.getPowerName());
                    st.setPower_local(p.getPowerLocal());
                    st.setHour(i);
                    if (i < 10) {
                        st.setTime(date + "  0" + i + ":00:00");
                    } else {
                        st.setTime(date + "  " + i + ":00:00");
                    }
                    st.setState("Error");
                    result.add(st);
                }
            }
            index++;
        }
        //4.对Inverter和result进行循环
        for (Inverter inver : list) {
            for (stationRunTableExample s : result) {
                if (inver.getCollectorSn().equals(s.getPower_sn())) {//sn匹配
                    if (inver.getUpdateTime() != null && inver.getUpdateTime().getHours() == s.getHour()) {//hour符合
                        if (inver.getUpdateTime().getMinutes() >= 45 && inver.getUpdateTime().getMinutes() <= 59) {//条件符合
                            s.setState("正常");
                        }
                    }
                }
            }
        }

        return result;
    }


    //与getInverterState一致,只不过是针对historyTable页面
    //          可直接用于historyTable.jsp页面的采集器
    public List<stationRunTableExample5> getCollectorStateInHistory(String power_id, String date) {
        List<stationRunTableExample5> result = new ArrayList<stationRunTableExample5>();

        //1.根据power_name,type,levelname来查询power_id;
        Power p = reportService.selectPowerById(power_id);
        //2.根据date，power_id站点查询该user下的所有inverter类（是一天的）
        List<Inverter> list = reportService.selectInverterByDate(date,p.getPowerId());
        //3.对每一个sn进行24小时循环，默认值:异常
        int index = 0;
        //4.对每个该电站的sn进行判断，有则不管，没有则创建
        List<String> li = new ArrayList<>();
        li.add(p.getPowerId());
        List<Collector> list_temp = reportService.selectCollectorWithPowerid(li );//获得该电站的每个sn
        for(Collector i_t : list_temp) {
            boolean te = false;//默认没有该sn
            for(Inverter i : list) {
                if(i_t.getCollectorSn().equals(i.getCollectorSn())) {
                    te = true;
                    break;
                }
            }
            if(!te) {
                Inverter in = new Inverter();
                in.setCollectorSn(i_t.getCollectorSn());
                list.add(in);
            }
        }
        //5.结果
        for(Inverter inver : list) {
            boolean temp = true;
            for(stationRunTableExample5 s : result) {
                if(inver.getCollectorSn().equals(s.getPower_sn())) {//如果有一个sn是匹配的，就不能24循环
                    temp = false;
                }
            }
            if(index == 0)  temp = true;
            if(temp) {
                for (int i = 0; i < 24; i++) {
                    stationRunTableExample5 st = new stationRunTableExample5();
                    st.setPower_sn(inver.getCollectorSn());
                    st.setPower_name(p.getPowerName());
                    st.setCommunication("GPRS");
                    st.setSignal("强");
                    st.setHour(i);
                    if (i < 10) {
                        st.setTime(date + "  0" + i + ":00:00");
                    } else {
                        st.setTime(date + "  " + i + ":00:00");
                    }
                    st.setState("Error");
                    result.add(st);
                }
            }
            index++;
        }
        //4.对Inverter和result进行循环
        for (Inverter inver : list) {
            for (stationRunTableExample5 s : result) {
                if (inver.getCollectorSn().equals(s.getPower_sn())) {//sn匹配
                    if (inver.getUpdateTime() != null && inver.getUpdateTime().getHours() == s.getHour()) {//hour符合
                        if (inver.getUpdateTime().getMinutes() >= 45 && inver.getUpdateTime().getMinutes() <= 59) {//条件符合
                            s.setState("正常");
                        }
                    }
                }
            }
        }

        return result;
    }


    //与getInverterState相似,只不过是针对historyTable页面
    //          可直接用于historyTable.jsp页面的逆变器
    //注意：如果里面为null，把它改为0
    public List<stationRunTableExample6> getInverterStateInHistory(String power_id, String date) {
        List<stationRunTableExample6> result = new ArrayList<stationRunTableExample6>();

        //1.根据power_name,type,levelname来查询power_id;
        Power p = reportService.selectPowerById(power_id);
        //2.根据date，power_id站点查询该user下的所有inverter类（是一天的）
        List<Inverter> list = reportService.selectInverterByDate(date,p.getPowerId());
        //3.对每一个sn进行24小时循环，默认值:异常
        int index = 0;
        //4.结果，直接把所有Inverter放入到stationRunTableExample6
        for(Inverter inver : list) {
            /*boolean temp = true;
            for(stationRunTableExample6 s : result) {
                if(inver.getCollectorSn().equals(s.getSn())) {
                    if(((inver.getDailyElectricity() == null) ? 0 : inver.getDailyElectricity()) < s.getElectricDay()) {//如果有一个sn是匹配，且日电量小，就不能放入result
                        temp = false;
                        break;
                    }
                }
            }
            if(temp) {*/
            stationRunTableExample6 st = new stationRunTableExample6();
            st.setSn(inver.getCollectorSn());
            st.setPassageway1("PV1");
            st.setPassageway2("PV2");
            st.setPassageway3("PV3");
            st.setDCvoltage1((inver.getDc1Voltage() == null) ? 0 : inver.getDc1Voltage());
            st.setDCvoltage2((inver.getDc2Voltage() == null) ? 0 : inver.getDc2Voltage());
            st.setDCvoltage3((inver.getDc3Voltage() == null) ? 0 : inver.getDc3Voltage());
            st.setDCcurrent1((inver.getDc1Current() == null) ? 0 : inver.getDc1Current());
            st.setDCcurrent2((inver.getDc2Current() == null) ? 0 : inver.getDc2Current());
            st.setDCcurrent3((inver.getDc3Current() == null) ? 0 : inver.getDc3Current());
            st.setACphase1("R");
            st.setACphase2("S");
            st.setACphase3("T");
            st.setACvoltage1((inver.getAc1Voltage() == null) ? 0 : inver.getAc1Voltage());
            st.setACvoltage2((inver.getAc2Voltage() == null) ? 0 : inver.getAc2Voltage());
            st.setACvoltage3((inver.getAc3Voltage() == null) ? 0 : inver.getAc3Voltage());
            st.setACcurrent1((inver.getAc1Current() == null) ? 0 : inver.getAc1Current());
            st.setACcurrent2((inver.getAc2Current() == null) ? 0 : inver.getAc2Current());
            st.setACcurrent3((inver.getAc3Current() == null) ? 0 : inver.getAc3Current());
            st.setACpower((inver.getCurrentPower() == null) ? 0 : inver.getCurrentPower());
            st.setFrequency((inver.getPowerGridFrequency() == null) ? 0 : inver.getPowerGridFrequency());
            st.setElectricDay(inver.getDailyElectricity());
            //总电量，由于其中一个没有的话，就会报错，所以要拆开
            //a:当天之前总电量，b:当天总电量
            float a = (reportService.selectElectricitySUMByDate(inver.getCollectorSn(),
                            new SimpleDateFormat("yyyy-MM-dd").format(inver.getUpdateDate()))
                                                    == null) ? 0 :
                        reportService.selectElectricitySUMByDate(inver.getCollectorSn(),
                            new SimpleDateFormat("yyyy-MM-dd").format(inver.getUpdateDate()));
            float b = (reportService.selectElectricityDAYByDate(inver.getCollectorSn(),
                            new SimpleDateFormat("yyyy-MM-dd").format(inver.getUpdateDate()),
                            new SimpleDateFormat("HH:mm:ss").format(inver.getUpdateTime()))
                                                    == null) ? 0 :
                        reportService.selectElectricityDAYByDate(inver.getCollectorSn(),
                            new SimpleDateFormat("yyyy-MM-dd").format(inver.getUpdateDate()),
                            new SimpleDateFormat("HH:mm:ss").format(inver.getUpdateTime()));
            st.setElectricCount(a+b);
            st.setTemperature((inver.getTemperature() == null) ? 0 : inver.getTemperature());
            String yyyy = new SimpleDateFormat("yyyy-MM-dd").format(inver.getUpdateDate());
            String hh = new SimpleDateFormat("HH:mm:ss").format(inver.getUpdateTime());
            st.setTime(yyyy+" "+hh);
            result.add(st);


        }

        return result;
    }


    //用于返回该电站该天每个时间段状态，对于faultStatisticalTable.jsp页面
    public List<stationRunTableExample4> getFaultState(String power_id, String date) {
        List<stationRunTableExample4> result = new ArrayList<stationRunTableExample4>();
        List<stationRunTableExample> list = getInverterState(power_id,date);//获得该电站所有设备的24小时的状态
        Power p = reportService.selectPowerById(power_id);//查出电站

        for(int i=0; i<24; i++) {
            stationRunTableExample4 sta = new stationRunTableExample4();
            sta.setPower_name(p.getPowerName());
            sta.setHour(i);
            if (i < 10) {
                sta.setFault_time(date + "  0" + i + ":00:00");
            } else {
                sta.setFault_time(date + "  " + i + ":00:00");
            }
            sta.setFault_content("正常");
            for(stationRunTableExample st : list) {
                if(st.getHour() == i && st.getState().equals("Error")) {//只要有1个sn（设备）故障，则power（电站）故障
                    sta.setFault_content("设备故障");
                    break;
                }
            }
            result.add(sta);
        }

        return result;
    }


    //直接针对historyTable.jsp页面的采集器，内有getInverterStateInHistory，且对时间、json进行了处理
    public JSONArray getCollectorInHistoryTable(String date1, String date2, String idTest, Model model) throws ParseException {
        String power_id="";
        String date = "";
        List<stationRunTableExample5> list = new ArrayList<>();

        if(!date1.equals("no") && !idTest.equals("no")) {
            power_id = idTest;
            date = date1;
            list = getCollectorStateInHistory(power_id,date);
        } else if(!date2.equals("no") && !idTest.equals("no")) {
            power_id = idTest;
            String[] da = date2.split("\\s-\\s");

            Calendar startCalendar = Calendar.getInstance();
            Calendar endCalendar = Calendar.getInstance();
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            Date startDate = df.parse(da[0]);
            startCalendar.setTime(startDate);
            Date endDate = df.parse(da[1]);
            endCalendar.setTime(endDate);
            while(true){
                if(startCalendar.getTimeInMillis() <= endCalendar.getTimeInMillis()){
                    list.addAll( getCollectorStateInHistory(power_id, df.format(startCalendar.getTime())) );
                }else{
                    break;
                }
                startCalendar.add(Calendar.DAY_OF_MONTH, 1);
            }

        }

        //转前台json
        //JSONObject result = new JSONObject();
        JSONArray res = new JSONArray();
        int index = 0;
        List<stationRunTableExample5> li = new ArrayList<>();
        for(int i=0; true; i++) {
            if(i >= list.size()) {
                JSONArray re = new JSONArray();
                re.addAll(li);
                JSONObject r = new JSONObject();
                r.put("con",re);
                res.add(r);
                break;
            }

            li.add(list.get(i));
            index++;
            if(index == 10) {
                index=0;

                JSONArray re = new JSONArray();
                re.addAll(li);
                JSONObject r = new JSONObject();
                r.put("con",re);
                res.add(r);

                li.clear();
            }

        }
        //result.put("data1",res);

        if(list.size()%10 == 0) {
            model.addAttribute("pageNum1",list.size()/10);//页数
        } else {
            model.addAttribute("pageNum1", list.size() / 10 + 1);//页数
        }

        return res;
    }


    //直接针对historyTable.jsp页面的逆变器，内有getInverterStateInHistory，且对时间、json进行了处理
    public JSONArray getInverterInHistoryTable(String date1, String date2, String idTest, Model model) throws ParseException {
        String power_id="";
        String date = "";
        List<stationRunTableExample6> list = new ArrayList<>();

        if(!date1.equals("no") && !idTest.equals("no")) {
            power_id = idTest;
            date = date1;
            list = getInverterStateInHistory(power_id,date);
        } else if(!date2.equals("no") && !idTest.equals("no")) {
            power_id = idTest;
            String[] da = date2.split("\\s-\\s");

            Calendar startCalendar = Calendar.getInstance();
            Calendar endCalendar = Calendar.getInstance();
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            Date startDate = df.parse(da[0]);
            startCalendar.setTime(startDate);
            Date endDate = df.parse(da[1]);
            endCalendar.setTime(endDate);
            while(true){
                if(startCalendar.getTimeInMillis() <= endCalendar.getTimeInMillis()){
                    list.addAll( getInverterStateInHistory(power_id, df.format(startCalendar.getTime())) );
                }else{
                    break;
                }
                startCalendar.add(Calendar.DAY_OF_MONTH, 1);
            }

        }

        //转前台json
        //JSONObject result = new JSONObject();
        JSONArray res = new JSONArray();
        int index = 0;
        List<stationRunTableExample6> li = new ArrayList<>();
        for(int i=0; true; i++) {
            if(i >= list.size()) {
                JSONArray re = new JSONArray();
                re.addAll(li);
                JSONObject r = new JSONObject();
                r.put("con",re);
                res.add(r);
                break;
            }

            li.add(list.get(i));
            index++;
            if(index == 2) {
                index=0;

                JSONArray re = new JSONArray();
                re.addAll(li);
                JSONObject r = new JSONObject();
                r.put("con",re);
                res.add(r);

                li.clear();
            }

        }
        //result.put("data2",res);

        if(list.size()%2 == 0) {
            model.addAttribute("pageNum2",list.size()/2);//页数
        } else {
            model.addAttribute("pageNum2", list.size() / 2 + 1);//页数
        }

        return res;
    }


    //转跳StationRunTable
    @RequestMapping("/InStationRunTable")
    public String InStationRunTable(@RequestParam(value="date1",defaultValue="no") String date1,
                                    @RequestParam(value="date2",defaultValue="no") String date2,
                                    @RequestParam(value="idTest",defaultValue="no") String idTest,
                                    HttpSession session, Model model) throws ParseException {

        String power_id="";
        String date = "";
        List<stationRunTableExample2> list = new ArrayList<>();

        if(!date1.equals("no") && !idTest.equals("no")) {
            power_id = idTest;
            date = date1;
            list = getPowerState(power_id,date);
        } else if(!date2.equals("no") && !idTest.equals("no")) {
            power_id = idTest;
            String[] da = date2.split("\\s-\\s");

            Calendar startCalendar = Calendar.getInstance();
            Calendar endCalendar = Calendar.getInstance();
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            Date startDate = df.parse(da[0]);
            startCalendar.setTime(startDate);
            Date endDate = df.parse(da[1]);
            endCalendar.setTime(endDate);
            while(true){
                if(startCalendar.getTimeInMillis() <= endCalendar.getTimeInMillis()){
                    list.addAll( getPowerState(power_id, df.format(startCalendar.getTime()) ) );
                }else{
                    break;
                }
                startCalendar.add(Calendar.DAY_OF_MONTH, 1);
            }

        }

        JSONObject result = new JSONObject();
        JSONArray res = new JSONArray();
        int index = 0;
        List<stationRunTableExample2> li = new ArrayList<>();
        for(int i=0; true; i++) {
            if(i >= list.size()) {
                JSONArray re = new JSONArray();
                re.addAll(li);
                JSONObject r = new JSONObject();
                r.put("con",re);
                res.add(r);
                break;
            }

            li.add(list.get(i));
            index++;
            if(index == 10) {
                index=0;

                JSONArray re = new JSONArray();
                re.addAll(li);
                JSONObject r = new JSONObject();
                r.put("con",re);
                res.add(r);

                li.clear();
            }

        }
        result.put("data",res);

        if(session.getAttribute("Power_type") != null) {
            model.addAttribute("powers",session.getAttribute("Power_type"));//所有电站
        }
        if(list.size()%10 == 0) {
            model.addAttribute("pageNum",list.size()/10);//页数
        } else {
            model.addAttribute("pageNum", list.size() / 10 + 1);//页数
        }
        model.addAttribute("result",result);//结果
        model.addAttribute("selected",idTest);//选择的电站id
        return "stationRunTable";
    }


    //转跳DeviceRunTable
    @RequestMapping("/InDeviceRunTable")
    public String InDeviceRunTable(@RequestParam(value="date1",defaultValue="no") String date1,
                                   @RequestParam(value="date2",defaultValue="no") String date2,
                                   @RequestParam(value="idTest",defaultValue="no") String idTest,
                                   HttpSession session, Model model) throws ParseException {

        String power_id="";
        String date = "";
        List<stationRunTableExample> list = new ArrayList<>();

        if(!date1.equals("no") && !idTest.equals("no")) {
            power_id = idTest;
            date = date1;
            list = getInverterState(power_id,date);
        } else if(!date2.equals("no") && !idTest.equals("no")) {
            power_id = idTest;
            String[] da = date2.split("\\s-\\s");

            Calendar startCalendar = Calendar.getInstance();
            Calendar endCalendar = Calendar.getInstance();
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            Date startDate = df.parse(da[0]);
            startCalendar.setTime(startDate);
            Date endDate = df.parse(da[1]);
            endCalendar.setTime(endDate);
            while(true){
                if(startCalendar.getTimeInMillis() <= endCalendar.getTimeInMillis()){
                    list.addAll( getInverterState(power_id, df.format(startCalendar.getTime())) );
                }else{
                    break;
                }
                startCalendar.add(Calendar.DAY_OF_MONTH, 1);
            }

        }

        //转前台json
        JSONObject result = new JSONObject();
        JSONArray res = new JSONArray();
        int index = 0;
        List<stationRunTableExample> li = new ArrayList<>();
        for(int i=0; true; i++) {
            if(i >= list.size()) {
                JSONArray re = new JSONArray();
                re.addAll(li);
                JSONObject r = new JSONObject();
                r.put("con",re);
                res.add(r);
                break;
            }

            li.add(list.get(i));
            index++;
            if(index == 10) {
                index=0;

                JSONArray re = new JSONArray();
                re.addAll(li);
                JSONObject r = new JSONObject();
                r.put("con",re);
                res.add(r);

                li.clear();
            }

        }
        result.put("data",res);

        if(session.getAttribute("Power_type") != null) {
            model.addAttribute("powers",session.getAttribute("Power_type"));//所有电站
        }
        if(list.size()%10 == 0) {
            model.addAttribute("pageNum",list.size()/10);//页数
        } else {
            model.addAttribute("pageNum", list.size() / 10 + 1);//页数
        }
        model.addAttribute("result",result);//结果
        model.addAttribute("selected",idTest);//选择的电站id
        return "deviceRunTable";
    }


    //转跳electricityTable.jsp页面
    /*
     * 发电量：1.选择日期，即该日期之前的，每天最大电量之和
     *        2.选择日期段：即该日期段，每天最大电量之和
     */
    @RequestMapping("/InElectricityTable")
    public String InElectricityTable(@RequestParam(value="date1",defaultValue="no") String date1,
                                     @RequestParam(value="date2",defaultValue="no") String date2,
                                     @RequestParam(value="idTest",defaultValue="no") String idTest,
                                     HttpSession session, Model model) throws ParseException {
        String power_id="";
        String date = "";
        List<stationRunTableExample3> list = new ArrayList<>();

        if(!date1.equals("no") && !idTest.equals("no")) {//选定1天
            power_id = idTest;
            date = date1;
            list = reportService.selectElecByPowerId(power_id,date);

            stationRunTableExample3 result = new stationRunTableExample3();
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            Date startDate = df.parse(date);
            result.setUpdate_date(startDate);
            float maxEle = 0;
            for(stationRunTableExample3 l : list) {
                maxEle += l.getPower_account();
            }
            result.setPower_account(maxEle);
            result.setPower_name(reportService.selectPowerById(power_id).getPowerName());
            list.clear();
            list.add(result);

        } else if(!date2.equals("no") && !idTest.equals("no")) {
            power_id = idTest;
            String[] da = date2.split("\\s-\\s");

            Calendar startCalendar = Calendar.getInstance();
            Calendar endCalendar = Calendar.getInstance();
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            Date startDate = df.parse(da[0]);
            startCalendar.setTime(startDate);
            Date endDate = df.parse(da[1]);
            endCalendar.setTime(endDate);

            List<stationRunTableExample3> result = new ArrayList<stationRunTableExample3>();
            while(true){//把 * - * 之间的所有天数循环
                if(startCalendar.getTimeInMillis() <= endCalendar.getTimeInMillis()){
                    result.addAll( reportService.selectElecByPowerId(power_id, df.format(startCalendar.getTime()) ) );//把该天的所有elec加入
                }else{
                    break;
                }
                startCalendar.add(Calendar.DAY_OF_MONTH, 1);
            }
            stationRunTableExample3 re = new stationRunTableExample3();
            float maxEle = 0;
            for(stationRunTableExample3 s : result) {
                maxEle += s.getPower_account();
            }
            re.setPower_account(maxEle);
            re.setPower_name(reportService.selectPowerById(power_id).getPowerName());
            list.add(re);

        }

        JSONObject result = new JSONObject();
        JSONArray res = new JSONArray();
        int index = 0;
        List<stationRunTableExample3> li = new ArrayList<>();
        for(int i=0; true; i++) {
            if(i >= list.size()) {
                JSONArray re = new JSONArray();
                re.addAll(li);
                JSONObject r = new JSONObject();
                r.put("con",re);
                res.add(r);
                break;
            }

            li.add(list.get(i));
            index++;
            if(index == 10) {
                index=0;

                JSONArray re = new JSONArray();
                re.addAll(li);
                JSONObject r = new JSONObject();
                r.put("con",re);
                res.add(r);

                li.clear();
            }

        }
        result.put("data",res);

        if(session.getAttribute("Power_type") != null) {
            model.addAttribute("powers",session.getAttribute("Power_type"));//所有电站
        }
        if(list.size()%10 == 0) {
            model.addAttribute("pageNum",list.size()/10);//页数
        } else {
            model.addAttribute("pageNum", list.size() / 10 + 1);//页数
        }
        model.addAttribute("result",result);//结果
        model.addAttribute("selected",idTest);//选择的电站id

        return "electricityTable";
    }


    //转跳faultStatisticalTable.jsp页面
    @RequestMapping("/InFaultStatisticalTable")
    public String InFaultStatisticalTable(@RequestParam(value="date1",defaultValue="no") String date1,
                                     @RequestParam(value="date2",defaultValue="no") String date2,
                                     @RequestParam(value="idTest",defaultValue="no") String idTest,
                                     HttpSession session, Model model) throws ParseException {
        String power_id="";
        String date = "";
        List<stationRunTableExample4> list = new ArrayList<>();

        if(!date1.equals("no") && !idTest.equals("no")) {
            power_id = idTest;
            date = date1;
            list = getFaultState(power_id,date);
        } else if(!date2.equals("no") && !idTest.equals("no")) {
            power_id = idTest;
            String[] da = date2.split("\\s-\\s");

            Calendar startCalendar = Calendar.getInstance();
            Calendar endCalendar = Calendar.getInstance();
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            Date startDate = df.parse(da[0]);
            startCalendar.setTime(startDate);
            Date endDate = df.parse(da[1]);
            endCalendar.setTime(endDate);
            while(true){
                if(startCalendar.getTimeInMillis() <= endCalendar.getTimeInMillis()){
                    list.addAll( getFaultState(power_id, df.format(startCalendar.getTime()) ) );
                }else{
                    break;
                }
                startCalendar.add(Calendar.DAY_OF_MONTH, 1);
            }

        }

        JSONObject result = new JSONObject();
        JSONArray res = new JSONArray();
        int index = 0;
        List<stationRunTableExample4> li = new ArrayList<>();
        for(int i=0; true; i++) {
            if(i >= list.size()) {
                JSONArray re = new JSONArray();
                re.addAll(li);
                JSONObject r = new JSONObject();
                r.put("con",re);
                res.add(r);
                break;
            }

            li.add(list.get(i));
            index++;
            if(index == 10) {
                index=0;

                JSONArray re = new JSONArray();
                re.addAll(li);
                JSONObject r = new JSONObject();
                r.put("con",re);
                res.add(r);

                li.clear();
            }

        }
        result.put("data",res);

        if(session.getAttribute("Power_type") != null) {
            model.addAttribute("powers",session.getAttribute("Power_type"));//所有电站
        }
        if(list.size()%10 == 0) {
            model.addAttribute("pageNum",list.size()/10);//页数
        } else {
            model.addAttribute("pageNum", list.size() / 10 + 1);//页数
        }
        model.addAttribute("result",result);//结果
        model.addAttribute("selected",idTest);//选择的电站id
        return "faultStatisticalTable";
    }


    //转跳historyTable.jsp页面
    @RequestMapping("/InHistoryTable")
    public String InHistoryTable(@RequestParam(value="date1",defaultValue="no") String date1,
                                 @RequestParam(value="date2",defaultValue="no") String date2,
                                 @RequestParam(value="idTest",defaultValue="no") String idTest,
                                 HttpSession session, Model model) throws ParseException {

        JSONObject result = new JSONObject();
        JSONArray result1 = getCollectorInHistoryTable(date1, date2, idTest, model);//获得historyTable.jsp页面的采集器的json
        JSONArray result2 = getInverterInHistoryTable(date1, date2, idTest, model);//获得historyTable.jsp页面的逆变器的json
        result.put("data1",result1);
        result.put("data2",result2);

        if(session.getAttribute("Power_type") != null) {
            model.addAttribute("powers",session.getAttribute("Power_type"));//所有电站
        }
        model.addAttribute("result",result);//结果
        model.addAttribute("selected",idTest);//选择的电站id

        return "historyTable";
    }


    /**
     * 导出Excel功能,stationRunTableExample
     * @param response
     * @throws Exception
     */
    @RequestMapping("/ExcelOut_Example")
    public void ExcelOut_Example(@RequestParam(value="date1",defaultValue="no") String date1,
                                 @RequestParam(value="date2",defaultValue="no") String date2,
                                 @RequestParam(value="idTest",defaultValue="no") String idTest,
                                 HttpServletResponse response) throws Exception {
        OutputStream os = null;
        List<stationRunTableExample> list = null;
        WritableSheet sheet = null;
        WritableWorkbook book = null;
        try {
            //设置弹出保存对话框
            response.setContentType("application/x-msdownload");
            response.setCharacterEncoding("utf-8");
            SimpleDateFormat date_formate = new SimpleDateFormat("yyyy-MM-dd");
            String fileName = "";
            //文件名,以当前时间为文件名
            if(date1.equals("no")) {
                fileName = date2.replaceAll(" ","");
            } else {
                fileName = date1;
            }
            response.setHeader("Content-Disposition", "attachment;filename="
                    + fileName + ".xls");//设置生成文件名字
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            os = response.getOutputStream();//获取响应输出流

            //获得list，由于里面有许多小不同，所以合成一个类比较麻烦
            String power_id = "";
            String date = "";
            list = new ArrayList<>();
            if(!date1.equals("no") && !idTest.equals("no")) {
                power_id = idTest;
                date = date1;
                list = getInverterState(power_id,date);
            } else if(!date2.equals("no") && !idTest.equals("no")) {
                power_id = idTest;
                String[] da = date2.split("\\s-\\s");

                Calendar startCalendar = Calendar.getInstance();
                Calendar endCalendar = Calendar.getInstance();
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                Date startDate = df.parse(da[0]);
                startCalendar.setTime(startDate);
                Date endDate = df.parse(da[1]);
                endCalendar.setTime(endDate);
                while(true){
                    if(startCalendar.getTimeInMillis() <= endCalendar.getTimeInMillis()){
                        list.addAll( getInverterState(power_id, df.format(startCalendar.getTime())) );
                    }else{
                        break;
                    }
                    startCalendar.add(Calendar.DAY_OF_MONTH, 1);
                }

            }

            /*
             * 制作excel表
             * */
            //初始化工作表
            book = Workbook.createWorkbook(os);

            //创建工作表的第一页,参数一:页名 参数二：页码
            sheet = book.createSheet("设备状态信息", 0);
            //设置字体种类和黑体显示，字体为Arial,字号大小为10，采用黑体显示
            WritableFont bold = new WritableFont(WritableFont.createFont("宋体"), 10);
            //生成一个单元格控制对象
            WritableCellFormat titleFormate = new WritableCellFormat(bold);
            // 单元格中的内容水平方向居中
            titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
            // 单元格的内容垂直方向居中
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
            titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
            //设置单元格背景色
            titleFormate.setBackground(Colour.GRAY_25);
            //设置列的宽度,第一个参数是列的角标，第二个列是对应列的宽度
            sheet.setColumnView(0, 30);
            //设置对应列
            sheet.addCell(new jxl.write.Label(0, 0, "电站名称", titleFormate));
            sheet.setColumnView(1, 30);
            sheet.addCell(new jxl.write.Label(1, 0, "设备SN", titleFormate));
            sheet.setColumnView(2, 30);
            sheet.addCell(new jxl.write.Label(2, 0, "设备状态", titleFormate));
            sheet.setColumnView(3, 30);
            sheet.addCell(new jxl.write.Label(3, 0, "状态时间", titleFormate));

            titleFormate = new WritableCellFormat(bold);
            titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
            titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            //添加内容
            int line = 1;//写数据时控制行，第一行设置成表头了，所以从第二行开始
            //添加信息
            for(stationRunTableExample s : list) {
                sheet.addCell(new jxl.write.Label(0,line,s.getPower_name()+""));
                sheet.addCell(new jxl.write.Label(1,line,s.getPower_sn()+""));
                sheet.addCell(new jxl.write.Label(2,line,s.getState()+""));
                sheet.addCell(new jxl.write.Label(3,line,s.getTime()+""));
                line++;
            }
            book.write();
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出Excel功能,stationRunTableExample2
     * @param response
     * @throws Exception
     */
    @RequestMapping("/ExcelOut_Example2")
    public void ExcelOut_Example2(@RequestParam(value="date1",defaultValue="no") String date1,
                              @RequestParam(value="date2",defaultValue="no") String date2,
                              @RequestParam(value="idTest",defaultValue="no") String idTest,
                              HttpServletResponse response) throws Exception {
        OutputStream os = null;
        List<stationRunTableExample2> list = null;
        WritableSheet sheet = null;
        WritableWorkbook book = null;
        try {
            //设置弹出保存对话框
            response.setContentType("application/x-msdownload");
            response.setCharacterEncoding("utf-8");
            SimpleDateFormat date_formate = new SimpleDateFormat("yyyy-MM-dd");

            String fileName = "";
            //文件名,以当前时间为文件名
            if(date1.equals("no")) {
                fileName = date2.replaceAll(" ","");
            } else {
                fileName = date1;
            }
            response.setHeader("Content-Disposition", "attachment;filename="
                    + fileName + ".xls");//设置生成文件名字
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            os = response.getOutputStream();//获取响应输出流

            //获得list，由于里面有许多小不同，所以合成一个类比较麻烦
            String power_id = "";
            String date = "";
            list = new ArrayList<>();
            if (!date1.equals("no") && !idTest.equals("no")) {
                power_id = idTest;
                date = date1;
                list = getPowerState(power_id, date);
            } else if (!date2.equals("no") && !idTest.equals("no")) {
                power_id = idTest;
                String[] da = date2.split("\\s-\\s");

                Calendar startCalendar = Calendar.getInstance();
                Calendar endCalendar = Calendar.getInstance();
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                Date startDate = df.parse(da[0]);
                startCalendar.setTime(startDate);
                Date endDate = df.parse(da[1]);
                endCalendar.setTime(endDate);
                while (true) {
                    if (startCalendar.getTimeInMillis() <= endCalendar.getTimeInMillis()) {
                        list.addAll(getPowerState(power_id, df.format(startCalendar.getTime())));
                    } else {
                        break;
                    }
                    startCalendar.add(Calendar.DAY_OF_MONTH, 1);
                }

            }

            /*
             * 制作excel表
             * */
            //初始化工作表
            book = Workbook.createWorkbook(os);

            //创建工作表的第一页,参数一:页名 参数二：页码
            sheet = book.createSheet("电站状态信息", 0);
            //设置字体种类和黑体显示，字体为Arial,字号大小为10，采用黑体显示
            WritableFont bold = new WritableFont(WritableFont.createFont("宋体"), 10);
            //生成一个单元格控制对象
            WritableCellFormat titleFormate = new WritableCellFormat(bold);
            // 单元格中的内容水平方向居中
            titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
            // 单元格的内容垂直方向居中
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
            titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
            //设置单元格背景色
            titleFormate.setBackground(Colour.GRAY_25);
            //设置列的宽度,第一个参数是列的角标，第二个列是对应列的宽度
            sheet.setColumnView(0, 30);
            //设置对应列
            sheet.addCell(new jxl.write.Label(0, 0, "电站名称", titleFormate));
            sheet.setColumnView(1, 30);
            sheet.addCell(new jxl.write.Label(1, 0, "电站地址", titleFormate));
            sheet.setColumnView(2, 30);
            sheet.addCell(new jxl.write.Label(2, 0, "电站状态", titleFormate));
            sheet.setColumnView(3, 30);
            sheet.addCell(new jxl.write.Label(3, 0, "状态时间", titleFormate));

            titleFormate = new WritableCellFormat(bold);
            titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
            titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);


            //添加内容
            int line = 1;//写数据时控制行，第一行设置成表头了，所以从第二行开始
            //添加信息
            for(stationRunTableExample2 s : list) {
                sheet.addCell(new jxl.write.Label(0,line,s.getPower_name()+""));
                sheet.addCell(new jxl.write.Label(1,line,s.getPower_local()+""));
                sheet.addCell(new jxl.write.Label(2,line,s.getState()+""));
                sheet.addCell(new jxl.write.Label(3,line,s.getTime()+""));
                line++;
            }
            book.write();
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出Excel功能,stationRunTableExample3
     * @param response
     * @throws Exception
     */
    @RequestMapping("/ExcelOut_Example3")
    public void ExcelOut_Example3(@RequestParam(value="date1",defaultValue="no") String date1,
                                 @RequestParam(value="date2",defaultValue="no") String date2,
                                 @RequestParam(value="idTest",defaultValue="no") String idTest,
                                 HttpServletResponse response) throws Exception {
        OutputStream os = null;
        List<stationRunTableExample3> list = null;
        WritableSheet sheet = null;
        WritableWorkbook book = null;
        try {
            //设置弹出保存对话框
            response.setContentType("application/x-msdownload");
            response.setCharacterEncoding("utf-8");
            SimpleDateFormat date_formate = new SimpleDateFormat("yyyy-MM-dd");

            String fileName = "";
            //文件名,以当前时间为文件名
            if(date1.equals("no")) {
                fileName = date2.replaceAll(" ","");
            } else {
                fileName = date1;
            }
            response.setHeader("Content-Disposition", "attachment;filename="
                    + fileName + ".xls");//设置生成文件名字
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            os = response.getOutputStream();//获取响应输出流

            //获得list，由于里面有许多小不同，所以合成一个类比较麻烦
            String power_id = "";
            String date = "";
            list = new ArrayList<>();
            if(!date1.equals("no") && !idTest.equals("no")) {//选定1天
                power_id = idTest;
                date = date1;
                list = reportService.selectElecByPowerId(power_id,date);

                stationRunTableExample3 result = new stationRunTableExample3();
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                Date startDate = df.parse(date);
                result.setUpdate_date(startDate);
                float maxEle = 0;
                for(stationRunTableExample3 l : list) {
                    maxEle += l.getPower_account();
                }
                result.setPower_account(maxEle);
                result.setPower_name(reportService.selectPowerById(power_id).getPowerName());
                list.clear();
                list.add(result);

            } else if(!date2.equals("no") && !idTest.equals("no")) {
                power_id = idTest;
                String[] da = date2.split("\\s-\\s");

                Calendar startCalendar = Calendar.getInstance();
                Calendar endCalendar = Calendar.getInstance();
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                Date startDate = df.parse(da[0]);
                startCalendar.setTime(startDate);
                Date endDate = df.parse(da[1]);
                endCalendar.setTime(endDate);

                List<stationRunTableExample3> result = new ArrayList<stationRunTableExample3>();
                while(true){//把 * - * 之间的所有天数循环
                    if(startCalendar.getTimeInMillis() <= endCalendar.getTimeInMillis()){
                        result.addAll( reportService.selectElecByPowerId(power_id, df.format(startCalendar.getTime()) ) );//把该天的所有elec加入
                    }else{
                        break;
                    }
                    startCalendar.add(Calendar.DAY_OF_MONTH, 1);
                }
                stationRunTableExample3 re = new stationRunTableExample3();
                float maxEle = 0;
                for(stationRunTableExample3 s : result) {
                    maxEle += s.getPower_account();
                }
                re.setPower_account(maxEle);
                re.setPower_name(reportService.selectPowerById(power_id).getPowerName());
                list.add(re);

            }

            /*
             * 制作excel表
             * */
            //初始化工作表
            book = Workbook.createWorkbook(os);

            //创建工作表的第一页,参数一:页名 参数二：页码
            sheet = book.createSheet("发电量统计", 0);
            //设置字体种类和黑体显示，字体为Arial,字号大小为10，采用黑体显示
            WritableFont bold = new WritableFont(WritableFont.createFont("宋体"), 10);
            //生成一个单元格控制对象
            WritableCellFormat titleFormate = new WritableCellFormat(bold);
            // 单元格中的内容水平方向居中
            titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
            // 单元格的内容垂直方向居中
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
            titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
            //设置单元格背景色
            titleFormate.setBackground(Colour.GRAY_25);
            //设置列的宽度,第一个参数是列的角标，第二个列是对应列的宽度
            sheet.setColumnView(0, 30);
            //设置对应列
            sheet.addCell(new jxl.write.Label(0, 0, "电站名称", titleFormate));
            sheet.setColumnView(1, 30);
            sheet.addCell(new jxl.write.Label(1, 0, "发电量Kw/h", titleFormate));

            titleFormate = new WritableCellFormat(bold);
            titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
            titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            //添加内容
            int line = 1;//写数据时控制行，第一行设置成表头了，所以从第二行开始
            //添加信息
            for(stationRunTableExample3 s : list) {
                sheet.addCell(new jxl.write.Label(0,line,s.getPower_name()+""));
                sheet.addCell(new jxl.write.Label(1,line,s.getPower_account()+""));
                line++;
            }
            book.write();
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出Excel功能,stationRunTableExample4
     * @param response
     * @throws Exception
     */
    @RequestMapping("/ExcelOut_Example4")
    public void ExcelOut_Example4(@RequestParam(value="date1",defaultValue="no") String date1,
                                  @RequestParam(value="date2",defaultValue="no") String date2,
                                  @RequestParam(value="idTest",defaultValue="no") String idTest,
                                  HttpServletResponse response) throws Exception {
        OutputStream os = null;
        List<stationRunTableExample4> list = null;
        WritableSheet sheet = null;
        WritableWorkbook book = null;
        try {
            //设置弹出保存对话框
            response.setContentType("application/x-msdownload");
            response.setCharacterEncoding("utf-8");
            SimpleDateFormat date_formate = new SimpleDateFormat("yyyy-MM-dd");

            String fileName = "";
            //文件名,以当前时间为文件名
            if(date1.equals("no")) {
                fileName = date2.replaceAll(" ","");
            } else {
                fileName = date1;
            }
            response.setHeader("Content-Disposition", "attachment;filename="
                    + fileName + ".xls");//设置生成文件名字
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            os = response.getOutputStream();//获取响应输出流

            //获得list，由于里面有许多小不同，所以合成一个类比较麻烦
            String power_id = "";
            String date = "";
            list = new ArrayList<>();
            if(!date1.equals("no") && !idTest.equals("no")) {
                power_id = idTest;
                date = date1;
                list = getFaultState(power_id,date);
            } else if(!date2.equals("no") && !idTest.equals("no")) {
                power_id = idTest;
                String[] da = date2.split("\\s-\\s");

                Calendar startCalendar = Calendar.getInstance();
                Calendar endCalendar = Calendar.getInstance();
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                Date startDate = df.parse(da[0]);
                startCalendar.setTime(startDate);
                Date endDate = df.parse(da[1]);
                endCalendar.setTime(endDate);
                while(true){
                    if(startCalendar.getTimeInMillis() <= endCalendar.getTimeInMillis()){
                        list.addAll( getFaultState(power_id, df.format(startCalendar.getTime()) ) );
                    }else{
                        break;
                    }
                    startCalendar.add(Calendar.DAY_OF_MONTH, 1);
                }

            }

            /*
             * 制作excel表
             * */
            //初始化工作表
            book = Workbook.createWorkbook(os);

            //创建工作表的第一页,参数一:页名 参数二：页码
            sheet = book.createSheet("故障情况统计表", 0);
            //设置字体种类和黑体显示，字体为Arial,字号大小为10，采用黑体显示
            WritableFont bold = new WritableFont(WritableFont.createFont("宋体"), 10);
            //生成一个单元格控制对象
            WritableCellFormat titleFormate = new WritableCellFormat(bold);
            // 单元格中的内容水平方向居中
            titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
            // 单元格的内容垂直方向居中
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
            titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
            //设置单元格背景色
            titleFormate.setBackground(Colour.GRAY_25);
            //设置列的宽度,第一个参数是列的角标，第二个列是对应列的宽度
            sheet.setColumnView(0, 30);
            //设置对应列
            sheet.addCell(new jxl.write.Label(0, 0, "电站名称", titleFormate));
            sheet.setColumnView(1, 30);
            sheet.addCell(new jxl.write.Label(1, 0, "故障时间", titleFormate));
            sheet.setColumnView(2, 30);
            sheet.addCell(new jxl.write.Label(2, 0, "故障内容", titleFormate));

            titleFormate = new WritableCellFormat(bold);
            titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
            titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            //添加内容
            int line = 1;//写数据时控制行，第一行设置成表头了，所以从第二行开始
            //添加信息
            for(stationRunTableExample4 s : list) {
                sheet.addCell(new jxl.write.Label(0,line,s.getPower_name()+""));
                sheet.addCell(new jxl.write.Label(1,line,s.getFault_time()+""));
                sheet.addCell(new jxl.write.Label(2,line,s.getFault_content()+""));
                line++;
            }
            book.write();
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出Excel功能,stationRunTableExample5
     * @param response
     * @throws Exception
     */
    @RequestMapping("/ExcelOut_Example5")
    public void ExcelOut_Example5(@RequestParam(value="date1",defaultValue="no") String date1,
                                  @RequestParam(value="date2",defaultValue="no") String date2,
                                  @RequestParam(value="idTest",defaultValue="no") String idTest,
                                  HttpServletResponse response) throws Exception {

        OutputStream os = null;
        List<stationRunTableExample5> list1 = null;
        List<stationRunTableExample6> list2 = null;
        WritableSheet sheet = null;
        WritableSheet sheet2 = null;
        WritableWorkbook book = null;
        try {
            //设置弹出保存对话框
            response.setContentType("application/x-msdownload");
            response.setCharacterEncoding("utf-8");
            SimpleDateFormat date_formate = new SimpleDateFormat("yyyy-MM-dd");

            String fileName = "";
            //文件名,以当前时间为文件名
            if(date1.equals("no")) {
                fileName = date2.replaceAll(" ","");
            } else {
                fileName = date1;
            }
            response.setHeader("Content-Disposition", "attachment;filename="
                    + fileName + ".xls");//设置生成文件名字
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            os = response.getOutputStream();//获取响应输出流

            //获得list采集器，由于里面有许多小不同，所以合成一个类比较麻烦
            String power_id="";
            String date = "";
            list1 = new ArrayList<>();
            list2 = new ArrayList<>();

            if(!date1.equals("no") && !idTest.equals("no")) {
                power_id = idTest;
                date = date1;
                list1 = getCollectorStateInHistory(power_id,date);
                list2 = getInverterStateInHistory(power_id,date);
            } else if(!date2.equals("no") && !idTest.equals("no")) {
                power_id = idTest;
                String[] da = date2.split("\\s-\\s");

                Calendar startCalendar = Calendar.getInstance();
                Calendar endCalendar = Calendar.getInstance();
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                Date startDate = df.parse(da[0]);
                startCalendar.setTime(startDate);
                Date endDate = df.parse(da[1]);
                endCalendar.setTime(endDate);
                while(true){
                    if(startCalendar.getTimeInMillis() <= endCalendar.getTimeInMillis()){
                        list1.addAll( getCollectorStateInHistory(power_id, df.format(startCalendar.getTime())) );
                        list2.addAll( getInverterStateInHistory(power_id, df.format(startCalendar.getTime())) );
                    }else{
                        break;
                    }
                    startCalendar.add(Calendar.DAY_OF_MONTH, 1);
                }

            }

            /*
             * 制作excel表
             * */
            //初始化工作表
            book = Workbook.createWorkbook(os);

            //创建工作表的第一页,参数一:页名 参数二：页码
            sheet = book.createSheet("采集器历史数据表", 0);
            sheet2 = book.createSheet("逆变器历史数据表", 1);
            //设置字体种类和黑体显示，字体为Arial,字号大小为10，采用黑体显示
            WritableFont bold = new WritableFont(WritableFont.createFont("宋体"), 10);
            //生成一个单元格控制对象
            WritableCellFormat titleFormate = new WritableCellFormat(bold);
            // 单元格中的内容水平方向居中
            titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
            // 单元格的内容垂直方向居中
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
            titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
            //设置单元格背景色
            titleFormate.setBackground(Colour.GRAY_25);
            //设置列的宽度,第一个参数是列的角标，第二个列是对应列的宽度
            sheet.setColumnView(0, 30);
            //设置对应列
            sheet.addCell(new jxl.write.Label(0, 0, "状态", titleFormate));
            sheet.setColumnView(1, 30);
            sheet.addCell(new jxl.write.Label(1, 0, "采集器序列号", titleFormate));
            sheet.setColumnView(2, 30);
            sheet.addCell(new jxl.write.Label(2, 0, "通讯方式", titleFormate));
            sheet.setColumnView(3, 30);
            sheet.addCell(new jxl.write.Label(3, 0, "通讯强度", titleFormate));
            sheet.setColumnView(4, 30);
            sheet.addCell(new jxl.write.Label(4, 0, "更新时间", titleFormate));

            //设置对应列
            /*
             *mergeCells(a,b,c,d) 单元格合并函数
             * a 单元格的列号
             * b 单元格的行号
             * c 从单元格[a,b]起，向下合并到c列
             * d 从单元格[a,b]起，向下合并到d行
             * 注：单元格的列号和行号都是从0开始计
             */
            sheet2.setColumnView(0, 30);
            sheet2.setColumnView(1, 10);
            sheet2.setColumnView(2, 10);
            sheet2.setColumnView(3, 10);
            sheet2.setColumnView(4, 10);
            sheet2.setColumnView(5, 10);
            sheet2.setColumnView(6, 10);
            sheet2.setColumnView(7, 10);
            sheet2.setColumnView(8, 10);
            sheet2.setColumnView(9, 20);
            sheet2.setColumnView(10, 20);
            sheet2.setColumnView(11, 20);
            sheet2.setColumnView(12, 30);
            sheet2.addCell(new jxl.write.Label(0, 0, "逆变器序号", titleFormate));
            sheet2.mergeCells(0,0,0,1);
            sheet2.addCell(new jxl.write.Label(1, 0, "直流输入", titleFormate));
            sheet2.mergeCells(1,0,3,0);
            sheet2.addCell(new jxl.write.Label(4, 0, "交流输入", titleFormate));
            sheet2.mergeCells(4,0,8,0);
            sheet2.addCell(new jxl.write.Label(9, 0, "当日电量(kWh)", titleFormate));
            sheet2.mergeCells(9,0,9,1);
            sheet2.addCell(new jxl.write.Label(10, 0, "累计电量(kWh)", titleFormate));
            sheet2.mergeCells(10,0,10,1);
            sheet2.addCell(new jxl.write.Label(11, 0, "逆变温度(℃)", titleFormate));
            sheet2.mergeCells(11,0,11,1);
            sheet2.addCell(new jxl.write.Label(12, 0, "更新时间", titleFormate));
            sheet2.mergeCells(12,0,12,1);
            sheet2.addCell(new jxl.write.Label(1, 1, "通道", titleFormate));
            sheet2.addCell(new jxl.write.Label(2, 1, "电压", titleFormate));
            sheet2.addCell(new jxl.write.Label(3, 1, "电流", titleFormate));
            sheet2.addCell(new jxl.write.Label(4, 1, "相位", titleFormate));
            sheet2.addCell(new jxl.write.Label(5, 1, "电压", titleFormate));
            sheet2.addCell(new jxl.write.Label(6, 1, "电流", titleFormate));
            sheet2.addCell(new jxl.write.Label(7, 1, "功率", titleFormate));
            sheet2.addCell(new jxl.write.Label(8, 1, "频率", titleFormate));

            titleFormate = new WritableCellFormat(bold);
            titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
            titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            titleFormate.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);


            //添加内容
            int line = 1;//写数据时控制行，第一行设置成表头了，所以从第二行开始
            //添加信息
            for(stationRunTableExample5 s : list1) {
                if("Error".equals(s.getState())) {
                    titleFormate.setBackground(jxl.format.Colour.RED);
                } else {
                    titleFormate.setBackground(jxl.format.Colour.WHITE);
                }
                sheet.addCell(new jxl.write.Label(0,line,s.getState()+""));
                sheet.addCell(new jxl.write.Label(1,line,s.getPower_sn()+""));
                sheet.addCell(new jxl.write.Label(2,line,s.getCommunication()+""));
                sheet.addCell(new jxl.write.Label(3,line,s.getSignal()+""));
                sheet.addCell(new jxl.write.Label(4,line,s.getTime()+""));

                line++;
            }
            //添加内容
            int line2 = 2;//写数据时控制行，第一行设置成表头了，所以从第三行开始
            //添加信息
            for(stationRunTableExample6 s : list2) {
                sheet2.addCell(new jxl.write.Label(0,line2,s.getSn()+""));
                sheet2.mergeCells(0,line2,0,line2+2);
                sheet2.addCell(new jxl.write.Label(1,line2,s.getPassageway1()+""));
                sheet2.addCell(new jxl.write.Label(1,line2+1,s.getPassageway2()+""));
                sheet2.addCell(new jxl.write.Label(1,line2+2,s.getPassageway3()+""));
                sheet2.addCell(new jxl.write.Label(2,line2,s.getDCvoltage1()+""));
                sheet2.addCell(new jxl.write.Label(2,line2+1,s.getDCvoltage2()+""));
                sheet2.addCell(new jxl.write.Label(2,line2+2,s.getDCvoltage3()+""));
                sheet2.addCell(new jxl.write.Label(3,line2,s.getDCcurrent1()+""));
                sheet2.addCell(new jxl.write.Label(3,line2+1,s.getDCcurrent2()+""));
                sheet2.addCell(new jxl.write.Label(3,line2+2,s.getDCcurrent3()+""));
                sheet2.addCell(new jxl.write.Label(4,line2,s.getACphase1()+""));
                sheet2.addCell(new jxl.write.Label(4,line2+1,s.getACphase2()+""));
                sheet2.addCell(new jxl.write.Label(4,line2+2,s.getACphase3()+""));
                sheet2.addCell(new jxl.write.Label(5,line2,s.getACvoltage1()+""));
                sheet2.addCell(new jxl.write.Label(5,line2+1,s.getACvoltage2()+""));
                sheet2.addCell(new jxl.write.Label(5,line2+2,s.getACvoltage3()+""));
                sheet2.addCell(new jxl.write.Label(6,line2,s.getACcurrent1()+""));
                sheet2.addCell(new jxl.write.Label(6,line2+1,s.getACcurrent2()+""));
                sheet2.addCell(new jxl.write.Label(6,line2+2,s.getACcurrent3()+""));
                sheet2.addCell(new jxl.write.Label(7,line2,s.getACpower()+""));
                sheet2.mergeCells(7,line2,7,line2+2);
                sheet2.addCell(new jxl.write.Label(8,line2,s.getFrequency()+""));
                sheet2.mergeCells(8,line2,8,line2+2);
                sheet2.addCell(new jxl.write.Label(9,line2,s.getElectricDay()+""));
                sheet2.mergeCells(9,line2,9,line2+2);
                sheet2.addCell(new jxl.write.Label(10,line2,s.getElectricCount()+""));
                sheet2.mergeCells(10,line2,10,line2+2);
                sheet2.addCell(new jxl.write.Label(11,line2,s.getTemperature()+""));
                sheet2.mergeCells(11,line2,11,line2+2);
                sheet2.addCell(new jxl.write.Label(12,line2,s.getTime()+""));
                sheet2.mergeCells(12,line2,12,line2+2);

                line2 += 3;
            }
            book.write();
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}
